﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using System.IO.Compression;
using System.Text;
using System.Windows.Forms;
using System.Xml;

namespace Sintel.SPReportingWizard
{
    class ScriptGenerator
    {
        public bool skipList(string Title)
        {
            return
                Title == "Web Part Gallery";
        }
        public ScriptGenerator(string webUrl, string sharePointDBName, string destinationDBName, bool createDatabase, string connString, string allWebsTableName, string sharePointVersion,
             string listNames, string ownerCode, RichTextBox scriptBox)
        {

            this.WebUrl = webUrl;
            this.SharepointDBName = sharePointDBName;
            this.ScriptBox = scriptBox;
            this.ConnStr = connString;
            this.AllWebsTableName = allWebsTableName;
            this.IsSP2007Mode = sharePointVersion == "2007";
            this.IsSP2010Mode = sharePointVersion == "2010";
            this.IsSP2013Mode = sharePointVersion == "2013";
            this.DestinationDBName = destinationDBName;
            this.CreateDatabase = createDatabase;

            if (listNames != "")
            {
                this.ListNames = new List<String>(listNames.Replace(", ", ",").Replace(" ,", ",").Split(','));
            }
            else
            {
                this.ListNames = new List<String>();
            }

            this.OwnerCode = ownerCode;
        }

        private string WebUrl;
        private string SharepointDBName;
        private string DestinationDBName;
        private bool CreateDatabase;
        private string ConnStr;
        private string AllWebsTableName;
        private bool IsSP2007Mode;
        private bool IsSP2010Mode;
        private bool IsSP2013Mode;


        private RichTextBox ScriptBox;
        private List<String> ListNames;
        private string OwnerCode;

        public void generate()
        {
            string listTitle = "";

            try
            {
                this.ScriptBox.Clear();
                bool firstLoop = true;
                SqlConnection conn = new SqlConnection(this.ConnStr);
                conn.Open();
                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = "SELECT l.tp_ID, l.tp_Title, l.tp_Fields, w.SiteId FROM " + this.SharepointDBName + ".dbo.AllLists l " +
                    " INNER JOIN " + this.SharepointDBName + "." + this.AllWebsTableName + " w ON w.Id = l.tp_WebId " +
                    " WHERE w.FullUrl LIKE '" + this.WebUrl + "'";
                if (this.IsSP2013Mode)
                    cmd.CommandText += " and l.tp_DeleteTransactionId= 0x";

                SqlDataReader reader = cmd.ExecuteReader();
                if (!reader.HasRows)
                {
                    MessageBox.Show("No database entry found for WebURL = " + this.WebUrl + ". Verify if the url is correct.");
                }
                while (reader.Read())
                {
                    Guid listId = reader.GetGuid(0);
                    listTitle = reader.GetString(1);

                    if (this.skipList(listTitle))
                        continue;

                    if (this.ListNames.Count == 0 || this.ListNames.Contains(listTitle))
                    {
                        try
                        {
                            string fieldsXMLStr = "";

                            if (this.IsSP2010Mode || this.IsSP2013Mode)
                            {
                                try
                                {
                                    SqlBytes fieldBytes = reader.GetSqlBytes(2);
                                    Stream readStream = fieldBytes.Stream;

                                    //skip first bytes
                                    readStream.Position += 12; // Compress Structure Header according to [MS -WSSFO2].
                                    readStream.Position += 2;  // Zlib header.

                                    using (DeflateStream deflateStream = new DeflateStream(readStream, CompressionMode.Decompress))
                                    {
                                        using (StreamReader sreader = new StreamReader(deflateStream))
                                        {
                                            fieldsXMLStr = sreader.ReadToEnd();
                                        }
                                    }
                                }
                                catch (InvalidDataException)
                                {
                                    SqlBytes fieldBytes = reader.GetSqlBytes(2);

                                    Stream readStream = fieldBytes.Stream;

                                    // stream is not compressed
                                    using (StreamReader sreader = new StreamReader(readStream))
                                    {
                                        fieldsXMLStr = sreader.ReadToEnd();

                                        if (!string.IsNullOrEmpty(fieldsXMLStr))
                                            fieldsXMLStr = fieldsXMLStr.Replace("\0", string.Empty);
                                    }
                                }
                            }
                            else
                            {
                                //SP2007 field is not compressed 
                                fieldsXMLStr = reader.GetString(2);
                            }

                            int pos = fieldsXMLStr.IndexOf("<Field");
                            if (pos > -1)
                            {
                                fieldsXMLStr = fieldsXMLStr.Substring(pos);
                            }

                            fieldsXMLStr = "<Root>" + fieldsXMLStr + "</Root>";
                            var siteID = reader.GetGuid(3).ToString();

                            //CREATE ONLY ONCE -- do not generate user lists - no need for the ORA
                            if (firstLoop)
                            {
                                firstLoop = false;
                                StringBuilder fun = new StringBuilder();

                                if (this.CreateDatabase && !string.IsNullOrEmpty(this.DestinationDBName))
                                {
                                    fun.AppendLine("CREATE DATABASE " + this.DestinationDBName);
                                    fun.AppendLine("GO");
                                }

                                if (!string.IsNullOrEmpty(this.DestinationDBName))
                                {
                                    fun.AppendLine("USE " + this.DestinationDBName);
                                    fun.AppendLine("GO");
                                }

                                //this is now available only for SP2007
                                if (IsSP2007Mode)
                                {
                                    fun.AppendLine("/***********************************************************");
                                    fun.AppendLine("GET getMultiUsers - returns comma separated list of users (for multiselect)");
                                    fun.AppendLine("************************************************************/");
                                    fun.AppendLine("IF OBJECT_ID (N'dbo.getMultiUsers" + this.OwnerCode + "', N'FN') IS NOT NULL");
                                    fun.AppendLine("    DROP FUNCTION dbo.getMultiUsers" + this.OwnerCode + ";");
                                    fun.AppendLine("GO");

                                    fun.AppendLine("CREATE FUNCTION dbo.getMultiUsers" + this.OwnerCode + "(@SiteID VARCHAR(36),@FieldID VARCHAR(36), @LeafName NVARCHAR(128))");
                                    fun.AppendLine("    RETURNS ");
                                    fun.AppendLine("	NVARCHAR(max) ");
                                    fun.AppendLine("WITH EXECUTE AS CALLER");
                                    fun.AppendLine("AS");
                                    fun.AppendLine("BEGIN");
                                    fun.AppendLine("	DECLARE");
                                    fun.AppendLine("		@userList NVARCHAR(MAX);");

                                    fun.AppendLine("	SELECT @userList = ISNULL(@userList + '; ', '') + u.tp_Title");
                                    fun.AppendLine("	FROM ");
                                    fun.AppendLine(String.Format("		{0}.dbo.AllUserDataJunctions jun WITH (NOLOCK)", this.SharepointDBName));
                                    fun.AppendLine(String.Format("		INNER JOIN {0}.dbo.UserInfo u WITH (NOLOCK) ON u.tp_ID = jun.tp_ID", this.SharepointDBName));
                                    fun.AppendLine("	WHERE");
                                    fun.AppendLine("		jun.tp_IsCurrentVersion = 1 AND jun.tp_DeleteTransactionId = 0");
                                    fun.AppendLine("		AND jun.tp_SiteID = @SiteID AND jun.tp_FieldId = @FieldID AND jun.tp_LeafName = @LeafName");

                                    fun.AppendLine("	RETURN @userList");

                                    fun.AppendLine("END");
                                    fun.AppendLine("GO");

                                    fun.AppendLine("GRANT EXECUTE ON dbo.getMultiUsers" + this.OwnerCode + " TO PUBLIC");
                                    fun.AppendLine("GO");
                                    fun.AppendLine("");
                                }

                                if (IsSP2013Mode)
                                {
                                    fun.AppendLine("/***********************************************************");
                                    fun.AppendLine("GET getMultiUsers - returns comma separated list of users (for multiselect)");
                                    fun.AppendLine("************************************************************/");
                                    fun.AppendLine("IF OBJECT_ID (N'dbo.getMultiUsers" + this.OwnerCode + "', N'FN') IS NOT NULL");
                                    fun.AppendLine("    DROP FUNCTION dbo.getMultiUsers" + this.OwnerCode + ";");
                                    fun.AppendLine("GO");

                                    fun.AppendLine("CREATE FUNCTION dbo.getMultiUsers" + this.OwnerCode + "(@DocID VARCHAR(36),@FieldID VARCHAR(36))");
                                    fun.AppendLine("    RETURNS ");
                                    fun.AppendLine("	NVARCHAR(max) ");
                                    fun.AppendLine("WITH EXECUTE AS CALLER");
                                    fun.AppendLine("AS");
                                    fun.AppendLine("BEGIN");
                                    fun.AppendLine("	DECLARE");
                                    fun.AppendLine("		@userList NVARCHAR(MAX);");

                                    fun.AppendLine("	SELECT @userList = ISNULL(@userList, '') + u.tp_Title  + ', '");
                                    fun.AppendLine("	FROM ");
                                    fun.AppendLine(String.Format("		{0}.dbo.AllUserDataJunctions jun WITH (NOLOCK)", this.SharepointDBName));
                                    fun.AppendLine(String.Format("		INNER JOIN {0}.dbo.UserInfo u WITH (NOLOCK) ON u.tp_ID = jun.tp_ID", this.SharepointDBName));
                                    fun.AppendLine("	WHERE");
                                    fun.AppendLine("		jun.tp_IsCurrentVersion = 1 AND jun.tp_DeleteTransactionId = 0");
                                    fun.AppendLine("		AND jun.tp_FieldId = @FieldID AND jun.tp_DocId = @DocID");

                                    fun.AppendLine("	RETURN @userList");

                                    fun.AppendLine("END");
                                    fun.AppendLine("GO");

                                    fun.AppendLine("GRANT EXECUTE ON dbo.getMultiUsers" + this.OwnerCode + " TO PUBLIC");
                                    fun.AppendLine("GO");
                                    fun.AppendLine("");




                                    fun.AppendLine("/***********************************************************");
                                    fun.AppendLine("GET getMultiLookups - returns comma separated list of lookup ids (for multiselect)");
                                    fun.AppendLine("************************************************************/");
                                    fun.AppendLine("IF OBJECT_ID (N'dbo.getMultiLookups" + this.OwnerCode + "', N'FN') IS NOT NULL");
                                    fun.AppendLine("    DROP FUNCTION dbo.getMultiLookups" + this.OwnerCode + ";");
                                    fun.AppendLine("GO");

                                    fun.AppendLine("CREATE FUNCTION dbo.getMultiLookups" + this.OwnerCode + "(@DocID VARCHAR(36),@FieldID VARCHAR(36))");
                                    fun.AppendLine("    RETURNS ");
                                    fun.AppendLine("	NVARCHAR(max) ");
                                    fun.AppendLine("WITH EXECUTE AS CALLER");
                                    fun.AppendLine("AS");
                                    fun.AppendLine("BEGIN");
                                    fun.AppendLine("	DECLARE");
                                    fun.AppendLine("		@lookupList NVARCHAR(MAX);");

                                    fun.AppendLine("	SELECT @lookupList = ISNULL(@lookupList, '') + CAST(r0.tp_ID as nvarchar)  + ', '");
                                    fun.AppendLine("	FROM ");
                                    fun.AppendLine(String.Format("		{0}.dbo.AllUserDataJunctions jun WITH (NOLOCK)", this.SharepointDBName));
                                    fun.AppendLine(String.Format("		INNER JOIN {0}.dbo.[AllLookupRelationships] alr WITH (NOLOCK) on jun.tp_FieldID = alr.FieldId", this.SharepointDBName));
                                    fun.AppendLine(String.Format("		INNER JOIN {0}.dbo.AllUserData r0 WITH (NOLOCK) on r0.tp_listID = alr.LookupListId and r0.tp_ID = jun.tp_ID", this.SharepointDBName));

                                    fun.AppendLine("	WHERE");
                                    fun.AppendLine("		jun.tp_IsCurrentVersion = 1 AND jun.tp_DeleteTransactionId = 0");
                                    fun.AppendLine("		AND jun.tp_FieldId = @FieldID AND jun.tp_DocId = @DocID");

                                    fun.AppendLine("	RETURN @lookupList");

                                    fun.AppendLine("END");
                                    fun.AppendLine("GO");

                                    fun.AppendLine("GRANT EXECUTE ON dbo.getMultiLookups" + this.OwnerCode + " TO PUBLIC");
                                    fun.AppendLine("GO");
                                    fun.AppendLine("");


                                }


                                /*********************************************/
                                /* CREATE SITE USERS */
                                /*********************************************/
                                fun.AppendLine("IF NOT (OBJECT_ID('dbo.spView_UserInfo" + this.OwnerCode + "') IS NULL)");
                                fun.AppendLine("BEGIN");
                                fun.AppendLine("	DROP VIEW dbo.spView_UserInfo" + this.OwnerCode + "");
                                fun.AppendLine("END");
                                fun.AppendLine("GO");
                                fun.AppendLine("");

                                fun.AppendLine("CREATE VIEW dbo.spView_UserInfo" + this.OwnerCode);
                                fun.AppendLine("AS");
                                fun.AppendLine("    SELECT * ");
                                if (this.OwnerCode.Length > 0)
                                {
                                    fun.AppendLine("    ,'" + this.OwnerCode + "' AS Owner");
                                }
                                fun.AppendLine("	FROM ");
                                fun.AppendLine("		" + this.SharepointDBName + ".dbo.UserInfo WITH (NOLOCK)");
                                fun.AppendLine("	WHERE");
                                fun.AppendLine("		tp_Deleted = 0 AND tp_IsActive = 1 AND tp_SiteID = '" + siteID + "'");
                                fun.AppendLine("GO");
                                fun.AppendLine("");

                                /*********************************************/
                                /* CREATE GROUPS */
                                /*********************************************/
                                fun.AppendLine("IF NOT (OBJECT_ID('dbo.spSysView_Groups" + this.OwnerCode + "') IS NULL)");
                                fun.AppendLine("BEGIN");
                                fun.AppendLine("	DROP VIEW dbo.spSysView_Groups" + this.OwnerCode + "");
                                fun.AppendLine("END");
                                fun.AppendLine("GO");
                                fun.AppendLine("");

                                fun.AppendLine("CREATE VIEW dbo.spSysView_Groups" + this.OwnerCode);
                                fun.AppendLine("AS");
                                fun.AppendLine("    SELECT * ");
                                if (this.OwnerCode.Length > 0)
                                {
                                    fun.AppendLine("    ,'" + this.OwnerCode + "' AS SOwner");
                                }
                                fun.AppendLine("	FROM ");
                                fun.AppendLine("		" + this.SharepointDBName + ".dbo.Groups WITH (NOLOCK)");
                                fun.AppendLine("	WHERE");
                                fun.AppendLine("		SiteID = '" + siteID + "'");
                                fun.AppendLine("GO");
                                fun.AppendLine("");

                                /*********************************************/
                                /* CREATE GROUP MEMBERSHIP */
                                /*********************************************/
                                fun.AppendLine("IF NOT (OBJECT_ID('dbo.spSysView_GroupMembership" + this.OwnerCode + "') IS NULL)");
                                fun.AppendLine("BEGIN");
                                fun.AppendLine("	DROP VIEW dbo.spSysView_GroupMembership" + this.OwnerCode + "");
                                fun.AppendLine("END");
                                fun.AppendLine("GO");
                                fun.AppendLine("");

                                fun.AppendLine("CREATE VIEW dbo.spSysView_GroupMembership" + this.OwnerCode);
                                fun.AppendLine("AS");
                                fun.AppendLine("    SELECT * ");
                                if (this.OwnerCode.Length > 0)
                                {
                                    fun.AppendLine("    ,'" + this.OwnerCode + "' AS Owner");
                                }
                                fun.AppendLine("	FROM ");
                                fun.AppendLine("		" + this.SharepointDBName + ".dbo.GroupMembership WITH (NOLOCK)");
                                fun.AppendLine("	WHERE");
                                fun.AppendLine("		SiteID = '" + siteID + "'");
                                fun.AppendLine("GO");
                                fun.AppendLine("");

                                /*********************************************/
                                /* CREATE ALL USER DATA JUNCTIONS */
                                /*********************************************/
                                fun.AppendLine("IF NOT (OBJECT_ID('dbo.spSysView_AllUsersDataJunctions" + this.OwnerCode + "') IS NULL)");
                                fun.AppendLine("BEGIN");
                                fun.AppendLine("	DROP VIEW dbo.spSysView_AllUsersDataJunctions" + this.OwnerCode + "");
                                fun.AppendLine("END");
                                fun.AppendLine("GO");
                                fun.AppendLine("");

                                fun.AppendLine("CREATE VIEW dbo.spSysView_AllUsersDataJunctions" + this.OwnerCode);
                                fun.AppendLine("AS");
                                fun.AppendLine("    SELECT *");
                                if (this.OwnerCode.Length > 0)
                                {
                                    fun.AppendLine("    ,'" + this.OwnerCode + "' AS Owner");
                                }
                                fun.AppendLine("	FROM ");
                                fun.AppendLine("		" + this.SharepointDBName + ".dbo.AllUserDataJunctions WITH (NOLOCK)");
                                fun.AppendLine("	WHERE");
                                fun.AppendLine("		tp_SiteId = '" + siteID + "' AND tp_DeleteTransactionId = 0 AND tp_IsCurrentVersion = 1");
                                fun.AppendLine("GO");
                                fun.AppendLine("");


                                addText(fun.ToString());
                            }


                            addComment(listTitle);
                            string sqlView = buildView(listTitle, listId, fieldsXMLStr, siteID);
                            addText(sqlView);
                        }
                        catch (Exception exc)
                        {
                            MessageBox.Show(string.Format("Error Generating view for table: {0}. Error: ", listTitle) + exc.Message);
                        }
                    }
                }
                conn.Close();

            }
            catch (Exception ex)
            {
                MessageBox.Show(string.Format("Critical error when generating view: {0}. Error: ", listTitle) + ex.Message);
            }
        }

        private void addComment(string value)
        {
            this.ScriptBox.AppendText("/***********************************************************");
            this.ScriptBox.AppendText("\n");
            this.ScriptBox.AppendText(value);
            this.ScriptBox.AppendText("\n");
            this.ScriptBox.AppendText("***********************************************************/");
            this.ScriptBox.AppendText("\n");
        }


        private void addText(string value)
        {
            this.ScriptBox.AppendText(value + "\n");
        }

        private void addGo()
        {
            addText("GO" + "\n");
        }

        private void addLineBreak()
        {
            addText("\n");
        }

        private string buildView(string listTitle, Guid listId, string fieldsXMLString, string siteId)
        {
            StringBuilder sql = new StringBuilder();

            StringBuilder sqlChild = new StringBuilder();
            try
            {
                XmlDocument doc = new XmlDocument();
                doc.Load(new StringReader(fieldsXMLString));
                string viewName = genViewName(listTitle);
                bool containsNextRowOridinal = false;

                sql.AppendLine("IF NOT (OBJECT_ID('dbo." + viewName + "') IS NULL)");
                sql.AppendLine("BEGIN");
                sql.AppendLine("	DROP VIEW dbo." + viewName);
                sql.AppendLine("END");
                sql.AppendLine("GO");
                sql.AppendLine("");

                sql.AppendLine("CREATE VIEW dbo." + viewName);
                sql.AppendLine("AS");
                sql.AppendLine("    SELECT");

                if (this.OwnerCode.Length > 0)
                {
                    sql.AppendLine("    '" + this.OwnerCode + "' AS Owner,");
                }
                /*
               XmlNodeList nodeList = null;
               
               if (this.IsSP2010Mode)
                   nodeList = doc.SelectNodes("/Root/Field[@ColName]");
               else
               {
                   nodeList = doc.SelectNodes("/Root/*[name() = 'FieldRef' or name() = 'Field'][@ColName]");
               }
               */
                XmlNodeList nodeList = nodeList = doc.SelectNodes("/Root/*[name() = 'FieldRef' or name() = 'Field'][@ColName]");

                int i = 1;

                //Standard Columns
                sql.AppendLine("        r0.tp_ID AS ID, r0.tp_Version AS Version, r0.tp_Author AS Author, r0.tp_Editor as Editor, r0.tp_Modified AS Modified, r0.tp_Created AS Created");
                if (this.IsSP2007Mode)
                    sql.AppendLine("        , r0.tp_LeafName AS LeafName");
                else
                    sql.AppendLine("        , r0.tp_DocId AS DocId");


                foreach (XmlNode node in nodeList)
                {
                    string colName = node.Attributes["ColName"].Value;
                    string staticName = "";

                    if (this.IsSP2010Mode || this.IsSP2013Mode)
                    {
                        if (node.Attributes["StaticName"] != null)
                            staticName = node.Attributes["StaticName"].Value;
                        else
                            staticName = node.Attributes["Name"].Value;
                    }
                    else
                        staticName = node.Attributes["Name"].Value;

                    var isMultiSelect = (node.Attributes["Mult"] != null) && (node.Attributes["Mult"].Value == "TRUE");
                    //if there is more then eg 16 integer fields in the list, SP creates a second ROW
                    var alias = "r0";
                    var isNextRowOridinal = (node.Attributes["RowOrdinal"] != null) && (node.Attributes["RowOrdinal"].Value == "1");
                    if (isNextRowOridinal)
                    {
                        containsNextRowOridinal = true;
                        alias = "r1";
                    }
                    if (isMultiSelect)
                    {
                        var fieldID = node.Attributes["ID"].Value.Replace("{", "").Replace("}", "");
                        var isUserList = node.Attributes["Type"].Value == "UserMulti";
                        var isLookupList = node.Attributes["Type"].Value == "LookupMulti";

                        if (isUserList)
                        {
                            if (IsSP2007Mode)
                            {
                                sql.AppendLine("        ,dbo.getMultiUsers" + this.OwnerCode + "('" + siteId + "', '" + fieldID + "', " + alias + ".tp_LeafName) AS [" + staticName + "]");
                                sql.AppendLine("       ," + alias + ".tp_LeafName AS [" + staticName + "_LeafName]");
                            }
                            else if (IsSP2013Mode)
                            {
                                sql.AppendLine("        ,dbo.getMultiUsers" + this.OwnerCode + "(r0.tp_DocId, '" + fieldID + "' ) AS [" + staticName + "]");
                            }

                            sqlChild.Append(this.buildListMultiUserView(siteId, fieldID, listTitle, staticName));

                        }
                        else if (isLookupList)
                        {
                            if (IsSP2013Mode)
                            {
                                sql.AppendLine("        ,dbo.getMultiLookups" + this.OwnerCode + "(r0.tp_DocId, '" + fieldID + "' ) AS [" + staticName + "]");
                            }

                            sqlChild.Append(this.buildListMultiLookupsView(siteId, fieldID, listTitle, staticName));

                        }

                        //FieldId & DocId required to get values from AllUsersDataJunctions
                        if (IsSP2010Mode || this.IsSP2013Mode)
                        {
                            sql.AppendLine("        ,'" + new Guid(node.Attributes["ID"].Value).ToString().ToUpper() + "' AS " + staticName + "_FieldId");
                        }




                    }
                    else
                    {
                        string fullFieldName = alias + "." + colName;
                        if (fullFieldName != "r0.tp_ID" && fullFieldName != "r0.tp_Author" && fullFieldName != "r0.tp_Editor" && fullFieldName != "r0.tp_Modified" &&
                            fullFieldName != "r0.tp_Created" && fullFieldName != "r0.tp_LeafName")
                        {
                            sql.AppendLine("        ," + fullFieldName + " AS [" + staticName + "]");
                        }
                    }

                    i += 1;

                }

                sql.AppendLine("FROM ");
                sql.AppendLine("    " + this.SharepointDBName + ".dbo.AllUserData r0 WITH (NOLOCK)");
                if (containsNextRowOridinal)
                {
                    sql.AppendLine("    INNER JOIN " + this.SharepointDBName + ".dbo.AllUserData r1 WITH (NOLOCK) ON r1.tp_ID = r0.tp_ID AND r1.tp_RowOrdinal = 1 AND r1.tp_ListId = '" + listId.ToString() + "' AND r1.tp_IsCurrentVersion = 1 and r1.tp_DeleteTransactionId = 0");
                }
                sql.AppendLine("WHERE");
                sql.AppendLine("    r0.tp_ListId = '" + listId.ToString() + "' AND r0.tp_IsCurrentVersion = 1 and r0.tp_DeleteTransactionId = 0 and r0.tp_RowOrdinal = 0");

                if (this.IsSP2013Mode)
                    sql.AppendLine("    AND r0.tp_IsCurrent = 1");

                sql.AppendLine("GO");
                sql.AppendLine("");
            }
            catch (Exception ex)
            {
                throw new Exception("Error in buildView: " + ex.Message);
            }

            //Add Dependent scripts if exist
            sql.Append(sqlChild.ToString());

            return sql.ToString();
        }

        private string buildListMultiUserView(string siteID, string fieldID, string listTitle, string fieldName)
        {
            StringBuilder sql = new StringBuilder();
            try
            {
                string viewName = genViewName(listTitle + "_" + fieldName);


                sql.AppendLine("IF NOT (OBJECT_ID('dbo." + viewName + "') IS NULL)");
                sql.AppendLine("BEGIN");
                sql.AppendLine("	DROP VIEW dbo." + viewName);
                sql.AppendLine("END");
                sql.AppendLine("GO");
                sql.AppendLine("");

                if (this.IsSP2007Mode)
                {
                    sql.AppendLine("CREATE VIEW dbo." + viewName);
                    sql.AppendLine("AS");
                    sql.AppendLine("	SELECT jun.tp_ID AS UserID, jun.tp_LeafName AS UserLeafName");
                    sql.AppendLine("	FROM ");
                    sql.AppendLine("		" + this.SharepointDBName + ".dbo.AllUserDataJunctions jun WITH (NOLOCK)");
                    sql.AppendLine("	WHERE");
                    sql.AppendLine("		jun.tp_IsCurrentVersion = 1 AND jun.tp_DeleteTransactionId = 0");
                    sql.AppendLine("		AND jun.tp_SiteID = '" + siteID + "' AND jun.tp_FieldId = '" + fieldID + "'");
                    sql.AppendLine("GO");
                    sql.AppendLine("");
                }
                else if (this.IsSP2013Mode)
                {
                    sql.AppendLine("CREATE VIEW dbo." + viewName);
                    sql.AppendLine("AS");
                    sql.AppendLine("	SELECT jun.tp_ID AS UserID, jun.tp_DocId AS UserDocId");
                    sql.AppendLine("	FROM ");
                    sql.AppendLine("		" + this.SharepointDBName + ".dbo.AllUserDataJunctions jun WITH (NOLOCK)");
                    sql.AppendLine("	WHERE");
                    sql.AppendLine("		jun.tp_IsCurrentVersion = 1 AND jun.tp_DeleteTransactionId = 0");
                    sql.AppendLine("		AND jun.tp_SiteID = '" + siteID + "' AND jun.tp_FieldId = '" + fieldID + "'");
                    sql.AppendLine("GO");
                    sql.AppendLine("");
                }

            }
            catch (Exception ex)
            {
                throw new Exception("Error in buildView: " + ex.Message);
            }
            return sql.ToString();
        }

        private string buildListMultiLookupsView(string siteID, string fieldID, string listTitle, string fieldName)
        {
            StringBuilder sql = new StringBuilder();
            try
            {
                string viewName = genViewName(listTitle + "_" + fieldName);


                sql.AppendLine("IF NOT (OBJECT_ID('dbo." + viewName + "') IS NULL)");
                sql.AppendLine("BEGIN");
                sql.AppendLine("	DROP VIEW dbo." + viewName);
                sql.AppendLine("END");
                sql.AppendLine("GO");
                sql.AppendLine("");

                if (this.IsSP2013Mode)
                {
                    sql.AppendLine("CREATE VIEW dbo." + viewName);
                    sql.AppendLine("AS");
                    sql.AppendLine("	SELECT jun.tp_ID AS ID, jun.tp_DocId AS DocId");
                    sql.AppendLine("	FROM ");
                    sql.AppendLine("		" + this.SharepointDBName + ".dbo.AllUserDataJunctions jun WITH (NOLOCK)");
                    sql.AppendLine("	WHERE");
                    sql.AppendLine("		jun.tp_IsCurrentVersion = 1 AND jun.tp_DeleteTransactionId = 0");
                    sql.AppendLine("		AND jun.tp_SiteID = '" + siteID + "' AND jun.tp_FieldId = '" + fieldID + "'");
                    sql.AppendLine("GO");
                    sql.AppendLine("");
                }

            }
            catch (Exception ex)
            {
                throw new Exception("Error in buildView: " + ex.Message);
            }
            return sql.ToString();
        }


        private string genName(string title)
        {
            return title.Replace(" ", "_").Replace("-", "_").Replace("/", "_").Replace("&", "_");

        }
        private string genViewName(string title)
        {
            if (this.OwnerCode.Length > 0)
            {
                return genName("spView " + this.OwnerCode + " " + title);
            }
            else
            {
                return genName("spView " + title);
            }

        }

    }
}
